/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package ufpr.ees.suhck.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Cassio
 */


public class Relatorio {

    private Connection conexao;
    
    public PreparedStatement stmt;

    public final static String DRIVER = "com.mysql.jdbc.Driver";
    public static String URL = "jdbc:mysql://localhost:3306/suhck";
    public final static String USER = "root";
    public final static String PASSWORD = "root";
    
    private String query = "";
    
    public Relatorio(){
        System.out.println("Conectando: " + URL);
        conectar();
    }
    
    public String agendamento(String data_inicial, String data_final){
        query = "SELECT a.data_agendamento, b.nome paciente, d.nome planoSaude, e.nome atendente, a.situacao_atendimento\n" +
                "FROM Agendamento a\n" +
                "LEFT JOIN Paciente b ON a.paciente_id = b.id\n" +
                "LEFT JOIN PacientePlano c ON c.paciente_id = b.id\n" +
                "LEFT JOIN PlanoSaude d ON d.id = c.planoSaude_id\n" +
                "LEFT JOIN Atendente e ON e.id = a.atendente_id\n" +
                "WHERE data_agendamento BETWEEN '" + data_inicial + "' AND '" + data_final + "' ORDER BY a.data_agendamento;";
        return query;
    }
    
    public String atendimento(String data_inicial, String data_final){
        query = "SELECT a.dataAtendimento, b.nome paciente, a.diagnostico, c.nome medico, TIMEDIFF(a.dataAtendimento, d.data_agendamento) tempoAtendimento\n" +
                "FROM Atendimento a\n" +
                "LEFT JOIN Paciente b ON a.paciente_id = b.id\n" +
                "LEFT JOIN Medico c ON c.id = a.medico_id\n" +
                "LEFT JOIN Agendamento d ON a.id = d.atendimento_id\n" +
                "WHERE dataAtendimento BETWEEN '" + data_inicial + "' AND '" + data_final + "' ORDER BY a.dataAtendimento;";
        return query;
    }
    
    public String cliente(String cpf){
        query = "SELECT a.nome paciente, b.dataAtendimento, c.nome medico, d.anamnese, b.diagnostico, b.receita\n" +
                "FROM Paciente a\n" +
                "LEFT JOIN Atendimento b ON a.id = b.paciente_id\n" +
                "LEFT JOIN Medico c ON c.id = b.medico_id\n" +
                "LEFT JOIN Agendamento d ON b.id = d.atendimento_id\n" +
                "WHERE a.cpf = '" + cpf + "' ORDER BY b.dataAtendimento;";
        return query;
    }
    
    private void conectar() {
        try {
            Class.forName(DRIVER);
            conexao = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("Conectado!");
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public boolean desconectar() {
        try {
            getConexao().close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Relatorio.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    public Connection getConexao() {
        return conexao;
    }
}
